package com.lance.shiro.mapper;

import com.lance.shiro.entity.IProperty;
import com.lance.shiro.entity.IPropertyList;
import com.lance.shiro.entity.ISolicitor;
import com.lance.shiro.entity.IUser;
import org.apache.catalina.User;
import org.apache.ibatis.annotations.*;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
public interface PropertyMapper {
    @Insert("insert into i_property (" +
            "bedroomCount,bathRoomCount," +
            "parkingCount," +
            "studyCount," +
            "storageCount," +
            "price," +
            "notes," +
            "aspect," +
            "supplement_notes," +
            "address," +
            "internalSize," +
            "externalSize," +
            "agentId," +
            "propertyListId," +
            "lot," +
            "buildingOverview," +
            "features," +
            "purchasePrice," +
            "sellingPrice," +
            "weeklyRent," +
            "managedDoma," +
            "type," +
            "textContractOfSale," +
            "textDepositForm," +
            "textSolicitor," +
            "textBillsCharges," +
            "textManagementAgreement," +
            "textOthers," +
            "purchaseDate," +
            "commenceDate," +
            "commission_rent," +
            "total_commission_sale," +
            "current_commission_sale," +
            "ipan_total_commission_sale," +
            "ipan_current_commission_sale," +
            "my_referral_paid," +
            "term_of_lease," +
            "ownerId,sale_note," +
            "sale_status," +
            "createTime," +
            "updateTime," +
            "status," +
            "solicitorId) values(" +
            "#{bedroomCount},#{bathRoomCount},#{parkingCount},#{studyCount},#{storageCount}," +
            "#{price}," +
            "#{notes}," +
            "#{aspect}," +
            "#{supplement_notes}," +
            "#{address}," +
            "#{internalSize}," +
            "#{externalSize}," +
            "#{agentId}," +
            "#{propertyListId}," +
            "#{lot}," +
            "#{buildingOverview}," +
            "#{features}," +
            "#{purchasePrice}," +
            "#{sellingPrice}," +
            "#{weeklyRent}," +
            "#{managedDoma}," +
            "#{type}," +
            "#{textContractOfSale}," +
            "#{textDepositForm}," +
            "#{textSolicitor}," +
            "#{textBillsCharges}," +
            "#{textManagementAgreement}," +
            "#{textOthers}," +
            "#{purchaseDate}," +
            "#{commenceDate}," +
            "#{commission_rent}," +
            "#{total_commission_sale}," +
            "#{current_commission_sale}," +
            "#{ipan_total_commission_sale}," +
            "#{ipan_current_commission_sale}," +
            "#{my_referral_paid}," +
            "#{term_of_lease}," +
            "#{ownerId}," +
            "#{sale_note}," +
            "#{sale_status}," +
            "now(),now(),#{status},#{solicitorId})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    public int add(IProperty property);

    @Update("update i_property set " +
            "address = #{address}," +
            "agentId = #{agentId}," +
            "propertyListId = #{propertyListId}," +
            "lot = #{lot}," +
            "buildingOverview = #{buildingOverview}," +
            "features = #{features}," +
            "purchasePrice = #{purchasePrice}," +
            "internalSize = #{internalSize}," +
            "externalSize = #{externalSize}," +
            "sellingPrice = #{sellingPrice}," +
            "weeklyRent = #{weeklyRent}," +
            "managedDoma = #{managedDoma}," +
            "type = #{type}," +
            "textContractOfSale = #{textContractOfSale}," +
            "textDepositForm = #{textDepositForm}," +
            "textSolicitor = #{textSolicitor}," +
            "textBillsCharges = #{textBillsCharges}," +
            "textManagementAgreement = #{textManagementAgreement}," +
            "textOthers = #{textOthers}," +
            "purchaseDate = #{purchaseDate}," +
            "commenceDate = #{commenceDate}," +
            "commission_rent = #{commission_rent}," +
            "total_commission_sale = #{total_commission_sale}," +
            "current_commission_sale = #{current_commission_sale}," +
            "ipan_total_commission_sale = #{ipan_total_commission_sale}," +
            "ipan_current_commission_sale = #{ipan_current_commission_sale}," +
            "my_referral_paid = #{my_referral_paid}," +
            "term_of_lease = #{term_of_lease}," +
            "ownerId = #{ownerId}," +
            "bedroomCount= #{bedroomCount}," +
            "bathRoomCount=#{bathRoomCount}," +
            "parkingCount=#{parkingCount}," +
            "studyCount=#{studyCount}," +
            "storageCount=#{storageCount}," +
            "price=#{price}," +
            "notes=#{notes}," +
            "aspect=#{aspect}," +
            "supplement_notes=#{supplement_notes}," +
            "status=#{status}," +
            "sale_status=#{sale_status}," +
            "sale_note=#{sale_note}," +
            "updateTime = now(), " +
            "solicitorId=#{solicitorId} where id=#{id} ")
    int update(IProperty property);

    @Update("delete from i_property where id=#{id} ")
    public void delete(int id);

    @Select("select * from i_property where id=#{id}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "agentId", column = "agentId"),
            @Result(property = "ownerId", column = "ownerId"),
            @Result(property = "solicitorId", column = "solicitorId"),
            @Result(property = "propertyListId", column = "propertyListId"),
            @Result(property = "agent", column = "agentId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "propertyList", column = "propertyListId", javaType = IPropertyList.class,
                    one = @One(select = "com.lance.shiro.mapper.PropertyListMapper.get")),
            @Result(property = "owner", column = "ownerId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "solicitor", column = "solicitorId", javaType = ISolicitor.class,
                    one = @One(select = "com.lance.shiro.mapper.SolicitorMapper.fetchById")),
    })
    public IProperty get(int id);

    //根据PropertyList查询
//    @Select("SELECT * FROM i_property where  propertyListId in (${propertyListId})  ")
//    @Results({
//            @Result(property = "id", column = "id"),
//            @Result(property = "agent", column = "agentId", javaType = IUser.class,
//                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
//            @Result(property = "propertyList", column = "propertyListId", javaType = IPropertyList.class,
//                    one = @One(select = "com.lance.shiro.mapper.PropertyListMapper.get")),
//            @Result(property = "owner", column = "ownerId", javaType = IUser.class,
//                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
//    })
//    ArrayList<IProperty> findAllByPropertyList(@Param("propertyListId") String propertyListId);
    @Select("SELECT * FROM i_property where ${attributes} ")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "agentId", column = "agentId"),
            @Result(property = "ownerId", column = "ownerId"),
            @Result(property = "solicitorId", column = "solicitorId"),
            @Result(property = "propertyListId", column = "propertyListId"),
            @Result(property = "agent", column = "agentId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "propertyList", column = "propertyListId", javaType = IPropertyList.class,
                    one = @One(select = "com.lance.shiro.mapper.PropertyListMapper.get")),
            @Result(property = "owner", column = "ownerId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "solicitor", column = "solicitorId", javaType = ISolicitor.class,
                    one = @One(select = "com.lance.shiro.mapper.SolicitorMapper.fetchById")),
    })
    ArrayList<IProperty> findAllByPropertyList(@Param("attributes") String attributes);

    //根据Agent查询
    @Select("SELECT * FROM i_property where  agentId in (${agentId})  ")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "agentId", column = "agentId"),
            @Result(property = "ownerId", column = "ownerId"),
            @Result(property = "solicitorId", column = "solicitorId"),
            @Result(property = "propertyListId", column = "propertyListId"),
            @Result(property = "agent", column = "agentId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "propertyList", column = "propertyListId", javaType = IPropertyList.class,
                    one = @One(select = "com.lance.shiro.mapper.PropertyListMapper.get")),
            @Result(property = "owner", column = "ownerId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "solicitor", column = "solicitorId", javaType = ISolicitor.class,
                    one = @One(select = "com.lance.shiro.mapper.SolicitorMapper.fetchById")),
    })
    ArrayList<IProperty> findAllByAgent(@Param("agentId") String agentId);

    //根据Owner查询
    @Select("SELECT * FROM i_property where ownerId in (${ownerId})  ")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "agentId", column = "agentId"),
            @Result(property = "ownerId", column = "ownerId"),
            @Result(property = "solicitorId", column = "solicitorId"),
            @Result(property = "propertyListId", column = "propertyListId"),
            @Result(property = "agent", column = "agentId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "propertyList", column = "propertyListId", javaType = IPropertyList.class,
                    one = @One(select = "com.lance.shiro.mapper.PropertyListMapper.get")),
            @Result(property = "owner", column = "ownerId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.get")),
            @Result(property = "solicitor", column = "solicitorId", javaType = ISolicitor.class,
                    one = @One(select = "com.lance.shiro.mapper.SolicitorMapper.fetchById")),
    })
    ArrayList<IProperty> findAllByOwner(@Param("ownerId") String ownerId);

    //查询
    //Zhan: Ignore all the text and unnecessary column inquiry
    @Select("SELECT id, agentId, ownerId, propertyListId, lot, purchasePrice, sellingPrice, weeklyRent, managedDoma, type, purchaseDate, commenceDate, commission_rent, total_commission_sale, current_commission_sale, term_of_lease, bedroomCount, bathRoomCount, parkingCount, price, status, createTime, updateTime, sale_status, ipan_total_commission_sale, ipan_current_commission_sale, my_referral_paid, internalSize, externalSize, studyCount, storageCount FROM i_property")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "agentId", column = "agentId"),
            @Result(property = "ownerId", column = "ownerId"),
            @Result(property = "solicitorId", column = "solicitorId"),
            @Result(property = "propertyListId", column = "propertyListId"),
            @Result(property = "agent", column = "agentId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.getForPropertyGrid")),
            @Result(property = "propertyList", column = "propertyListId", javaType = IPropertyList.class,
                    one = @One(select = "com.lance.shiro.mapper.PropertyListMapper.getForPropertyGrid")),
            @Result(property = "owner", column = "ownerId", javaType = IUser.class,
                    one = @One(select = "com.lance.shiro.mapper.UserMapper.getForPropertyGrid")),
            @Result(property = "solicitor", column = "solicitorId", javaType = ISolicitor.class,
                    one = @One(select = "com.lance.shiro.mapper.SolicitorMapper.fetchById")),
    })
    ArrayList<IProperty> findAll();


    @Update("update i_property set ${attributes}  ,updateTime = now() where id=#{id} ")
    int updateAttribute(@Param("id") int id, @Param("attributes") String attributes);

    @Select("SELECT bedroomCount,bathRoomCount,parkingCount,studyCount, storageCount,price,group_concat(distinct notes SEPARATOR '\\r\\n') notes,count(1) qty FROM i_property where propertyListId = #{propertyListId} " +
            " group by bedroomCount,bathRoomCount,parkingCount,studyCount, storageCount, price,propertyListId ")
    ArrayList<HashMap<String, Object>> findAllLotType(int propertyListId);

    @Select("select count(1) as totalCommisionUnit,IFNULL (sum(commission_rent),0) as monthlyCommission from i_property where agentId = #{userId} and ownerId is not null and type = 'rent' ")
    Map<String, String> findAgentPropertyManagement(String userId);

    @Select("select count(1) as totalCommisionUnit,IFNULL (sum(total_commission_sale),0) as totalCommission from i_property where agentId = #{userId} and type = 'sale' and sale_status IN ('deposit_taken','contract_exchanged','settled')")
    Map<String, String> findAgentSalesyRecord(String userId);

    @Select("select i_property.lot, i_property_list.buildingAddress as address,IFNULL(i_property.current_commission_sale,0) as current_commission ,IFNULL(i_property.total_commission_sale,0) as total_commission, IFNULL(i_property.my_referral_paid,0) from i_property,i_property_list where i_property.agentId = #{agentId}  and i_property.type = 'sale' and i_property.propertyListId = i_property_list.id")
    List<Map<String, String>> findAllSaleByAgent(int agentId);

    @Select("select IFNULL (sum(current_commission_sale),0) as sum_current_commission ,IFNULL (sum(total_commission_sale),0) as sum_total_commission,IFNULL (sum(my_referral_paid),0) as sum_my_referral_paid from i_property where agentId = #{agentId} and ownerId is not null and type = 'sale' ")
    Map<String, String> findAgentSalesTotalCommission(int agentId);

    @Update("update i_property set sale_status=#{sale_status} ,sale_note=#{sale_note}  ,agentId=0,updateTime = now() where id=#{id} ")
    int updateStatusSaleNote(@Param("id") int id, @Param("sale_status") String sale_status, @Param("sale_note") String sale_note);

}
